{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "source": [
        "<img src=\"https://huggingface.co/datasets/huggingface/brand-assets/resolve/main/hf-logo.png\" height=100><img src=\"https://github.com/chdb-io/chdb/raw/pybind/docs/_static/snake-chdb.png\" height=100>\n",
        "\n",
        "Hugging Face **[Datasets Server](https://huggingface.co/datasets)** is a lightweight web API for visualizing and exploring all types of datasets - computer vision, speech, text, and tabular - stored on  Hugging Face! The Datasets Server performs automatic conversion of all publicly available datasets into **Parquet files**.\n",
        "\n",
        "Let's access **Datasets** using **[chDB](https://chdb.io)** and its native parquet processing capabilities!\n",
        "\n",
        "https://huggingface.co/docs/datasets-server/index"
      ],
      "metadata": {
        "id": "jVblL6BQ1jue"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "U9LPOma1tG9y",
        "outputId": "c22f11da-f156-4d68-f6f1-c784105fb931"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
            "Requirement already satisfied: pyarrow in /usr/local/lib/python3.10/dist-packages (9.0.0)\n",
            "Requirement already satisfied: pandas in /usr/local/lib/python3.10/dist-packages (1.5.3)\n",
            "Requirement already satisfied: numpy>=1.16.6 in /usr/local/lib/python3.10/dist-packages (from pyarrow) (1.22.4)\n",
            "Requirement already satisfied: python-dateutil>=2.8.1 in /usr/local/lib/python3.10/dist-packages (from pandas) (2.8.2)\n",
            "Requirement already satisfied: pytz>=2020.1 in /usr/local/lib/python3.10/dist-packages (from pandas) (2022.7.1)\n",
            "Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.10/dist-packages (from python-dateutil>=2.8.1->pandas) (1.16.0)\n",
            "Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
            "Collecting chdb\n",
            "  Downloading chdb-0.10.2-cp310-cp310-manylinux_2_5_x86_64.manylinux1_x86_64.manylinux_2_17_x86_64.manylinux2014_x86_64.whl (109.0 MB)\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m109.0/109.0 MB\u001b[0m \u001b[31m8.4 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25hInstalling collected packages: chdb\n",
            "Successfully installed chdb-0.10.2\n"
          ]
        }
      ],
      "source": [
        "!pip install pyarrow pandas\n",
        "!pip install chdb --pre --upgrade"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%%time\n",
        "import chdb\n",
        "\n",
        "# Dataset URL: https://huggingface.co/datasets/blog_authorship_corpus\n",
        "url = \"https://cdn-lfs.huggingface.co/datasets/blog_authorship_corpus/8ddb3aa485ba50c75b6eabbd1c81b20a36ca54656fef6d2050943e9779afd215?response-content-disposition=attachment%3B+filename*%3DUTF-8%27%27blog_authorship_corpus-train-00000-of-00002.parquet%3B+filename%3D%22blog_authorship_corpus-train-00000-of-00002.parquet%22%3B&Expires=1687121444&Policy=eyJTdGF0ZW1lbnQiOlt7IlJlc291cmNlIjoiaHR0cHM6Ly9jZG4tbGZzLmh1Z2dpbmdmYWNlLmNvL2RhdGFzZXRzL2Jsb2dfYXV0aG9yc2hpcF9jb3JwdXMvOGRkYjNhYTQ4NWJhNTBjNzViNmVhYmJkMWM4MWIyMGEzNmNhNTQ2NTZmZWY2ZDIwNTA5NDNlOTc3OWFmZDIxNT9yZXNwb25zZS1jb250ZW50LWRpc3Bvc2l0aW9uPSoiLCJDb25kaXRpb24iOnsiRGF0ZUxlc3NUaGFuIjp7IkFXUzpFcG9jaFRpbWUiOjE2ODcxMjE0NDR9fX1dfQ__&Signature=dWDhhI656rMTECLHF3wWugL4LhKtiVWy30tZxtoaaAP16rZTNynSt0jS7wrKU81EZmAGEpWnOb3LMKEodW9mB%7EpQ5tsx3%7EElq0aZybHoVwcBeV3Chp34rwOEu1PRDpVBTSfqmVImMFloZ0le%7EH%7ErNtX8m4IeSaUiaV3hCm-AbWpuEXxu6L0tvQQVtAjtYiySR-IkS7eG00oSHw-ZOEC%7Ezj-ctEWN3G8gIIqKe2Gsg70LJNhocx9rBoqWTWgkKyteeLtImws3zqdsWybZtEqkNk9RmO6A1aiCfqpHnbWo9ZeoHWNvIJ5CZ%7EONHgC7NG7Wndpy41KsTn7mL5%7EaizLsJg__&Key-Pair-Id=KVTP0A1DKRTAX\"\n",
        "\n",
        "# Query Dataset using SQL and ClickHouse:\n",
        "query = f\"\"\"SELECT horoscope, count(*) as count, avg(length(text)) as avg_blog_length\n",
        "FROM url('{url}', Parquet)\n",
        "GROUP BY horoscope\n",
        "ORDER BY avg_blog_length DESC\n",
        "LIMIT 12;\"\"\"\n",
        "chdb.query(query, 'Dataframe')\n",
        "\n"
      ],
      "metadata": {
        "id": "n-QJA2RPtHmk",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 397
        },
        "outputId": "756c8a5e-d1c8-4e04-e91c-af2be65fc7a7"
      },
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "CPU times: user 1.8 s, sys: 275 ms, total: 2.07 s\n",
            "Wall time: 4.05 s\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "         horoscope  count  avg_blog_length\n",
              "0      b'Aquarius'  34062      1134.379426\n",
              "1        b'Cancer'  41509      1100.819172\n",
              "2     b'Capricorn'  33961      1078.648450\n",
              "3         b'Libra'  40302      1074.620317\n",
              "4           b'Leo'  40587      1068.775987\n",
              "5         b'Virgo'  45975      1030.875345\n",
              "6   b'Sagittarius'  31642      1020.173124\n",
              "7       b'Scorpio'  38264      1014.192635\n",
              "8        b'Taurus'  40634      1011.540139\n",
              "9        b'Gemini'  33251      1004.638537\n",
              "10       b'Pisces'  36391       983.642714\n",
              "11        b'Aries'  39422       970.182994"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-ad6d0177-23f1-4a2d-803b-cab69b33570d\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>horoscope</th>\n",
              "      <th>count</th>\n",
              "      <th>avg_blog_length</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>b'Aquarius'</td>\n",
              "      <td>34062</td>\n",
              "      <td>1134.379426</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>b'Cancer'</td>\n",
              "      <td>41509</td>\n",
              "      <td>1100.819172</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>b'Capricorn'</td>\n",
              "      <td>33961</td>\n",
              "      <td>1078.648450</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>b'Libra'</td>\n",
              "      <td>40302</td>\n",
              "      <td>1074.620317</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>b'Leo'</td>\n",
              "      <td>40587</td>\n",
              "      <td>1068.775987</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>b'Virgo'</td>\n",
              "      <td>45975</td>\n",
              "      <td>1030.875345</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>b'Sagittarius'</td>\n",
              "      <td>31642</td>\n",
              "      <td>1020.173124</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>b'Scorpio'</td>\n",
              "      <td>38264</td>\n",
              "      <td>1014.192635</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>b'Taurus'</td>\n",
              "      <td>40634</td>\n",
              "      <td>1011.540139</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>b'Gemini'</td>\n",
              "      <td>33251</td>\n",
              "      <td>1004.638537</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>b'Pisces'</td>\n",
              "      <td>36391</td>\n",
              "      <td>983.642714</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>b'Aries'</td>\n",
              "      <td>39422</td>\n",
              "      <td>970.182994</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-ad6d0177-23f1-4a2d-803b-cab69b33570d')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-ad6d0177-23f1-4a2d-803b-cab69b33570d button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-ad6d0177-23f1-4a2d-803b-cab69b33570d');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 10
        }
      ]
    }
  ]
}